// Category.js - 视频类别模型
const { sequelize } = require('../config/db');

class Category {
  constructor(id, name, alias, icon, description, parentId, level, sortOrder, videoCount, status, createdAt, updatedAt, deletedAt) {
    this.id = id;
    this.name = name;
    this.alias = alias;
    this.icon = icon;
    this.description = description;
    this.parentId = parentId;
    this.level = level;
    this.sortOrder = sortOrder;
    this.videoCount = videoCount;
    this.status = status;
    this.createdAt = createdAt;
    this.updatedAt = updatedAt;
    this.deletedAt = deletedAt;
  }

  // 根据ID获取类别
  static async getById(id) {
    try {
      const rows = await sequelize.query('SELECT * FROM categories WHERE id = ? AND deleted_at IS NULL', {
        replacements: [id],
        type: sequelize.QueryTypes.SELECT
      });
      if (rows.length === 0) return null;
      const category = rows[0];
      return new Category(
        category.id,
        category.name,
        category.alias,
        category.icon,
        category.description,
        category.parent_id,
        category.level,
        category.sort_order,
        category.video_count,
        category.status,
        category.created_at,
        category.updated_at,
        category.deleted_at
      );
    } catch (error) {
      console.error('获取类别失败:', error);
      throw error;
    }
  }

  // 获取所有启用的类别
  static async getAll() {
    try {
      const rows = await sequelize.query(
        'SELECT * FROM categories WHERE status = 1 AND deleted_at IS NULL ORDER BY sort_order ASC, id ASC',
        { type: sequelize.QueryTypes.SELECT }
      );
      return rows.map(row => new Category(
        row.id,
        row.name,
        row.alias,
        row.icon,
        row.description,
        row.parent_id,
        row.level,
        row.sort_order,
        row.video_count,
        row.status,
        row.created_at,
        row.updated_at,
        row.deleted_at
      ));
    } catch (error) {
      console.error('获取所有类别失败:', error);
      throw error;
    }
  }

  // 获取所有类别（包括禁用，管理员用）
  static async getAllForAdmin() {
    try {
      const rows = await sequelize.query(
        'SELECT * FROM categories WHERE deleted_at IS NULL ORDER BY sort_order ASC, id ASC',
        { type: sequelize.QueryTypes.SELECT }
      );
      return rows.map(row => new Category(
        row.id,
        row.name,
        row.alias,
        row.icon,
        row.description,
        row.parent_id,
        row.level,
        row.sort_order,
        row.video_count,
        row.status,
        row.created_at,
        row.updated_at,
        row.deleted_at
      ));
    } catch (error) {
      console.error('获取所有类别失败:', error);
      throw error;
    }
  }

  // 创建新类别
  static async create(name, alias, icon, description, parentId = null, level = 1, sortOrder = 0) {
    try {
      const now = new Date();
      // 特殊处理parentId为null的情况，使用IS NULL语法
      let query;
      let replacements;
      
      if (parentId === null) {
        query = `INSERT INTO categories 
         (name, alias, icon, description, parent_id, level, sort_order, video_count, status, created_at, updated_at)
         VALUES (?, ?, ?, ?, NULL, ?, ?, 0, 1, ?, ?)`;
        replacements = [name, alias, icon, description, level, sortOrder, now, now];
      } else {
        query = `INSERT INTO categories 
         (name, alias, icon, description, parent_id, level, sort_order, video_count, status, created_at, updated_at)
         VALUES (?, ?, ?, ?, ?, ?, ?, 0, 1, ?, ?)`;
        replacements = [name, alias, icon, description, parentId, level, sortOrder, now, now];
      }
      
      const result = await sequelize.query(query, {
        replacements: replacements,
        type: sequelize.QueryTypes.INSERT
      });
      // 获取最后插入的ID
        const [insertIdResult] = await sequelize.query('SELECT LAST_INSERT_ID() as id');
        return await Category.getById(insertIdResult[0].id);
    } catch (error) {
      console.error('创建类别失败:', error);
      throw error;
    }
  }

  // 更新类别
  static async update(id, data) {
    try {
      const fields = [];
      const values = [];
      
      if (data.name !== undefined) { fields.push('name = ?'); values.push(data.name); }
      if (data.alias !== undefined) { fields.push('alias = ?'); values.push(data.alias); }
      if (data.icon !== undefined) { fields.push('icon = ?'); values.push(data.icon); }
      if (data.description !== undefined) { fields.push('description = ?'); values.push(data.description); }
      if (data.parentId !== undefined) { fields.push('parent_id = ?'); values.push(data.parentId); }
      if (data.level !== undefined) { fields.push('level = ?'); values.push(data.level); }
      if (data.sortOrder !== undefined) { fields.push('sort_order = ?'); values.push(data.sortOrder); }
      if (data.status !== undefined) { fields.push('status = ?'); values.push(data.status); }
      if (data.videoCount !== undefined) { fields.push('video_count = ?'); values.push(data.videoCount); }
      
      fields.push('updated_at = ?');
      values.push(new Date());
      values.push(id);
      
      const result = await sequelize.query(
        `UPDATE categories SET ${fields.join(', ')} WHERE id = ?`,
        {
          replacements: values,
          type: sequelize.QueryTypes.UPDATE
        }
      );
      
      if (result.affectedRows === 0) return null;
      return await Category.getById(id);
    } catch (error) {
      console.error('更新类别失败:', error);
      throw error;
    }
  }

  // 软删除类别
  static async delete(id) {
    try {
      const result = await sequelize.query(
        'UPDATE categories SET deleted_at = NOW() WHERE id = ? AND deleted_at IS NULL',
        {
          replacements: [id],
          type: sequelize.QueryTypes.UPDATE
        }
      );
      // 对于更新操作，我们只需要知道是否成功执行
        return true;
    } catch (error) {
      console.error('删除类别失败:', error);
      throw error;
    }
  }
}

module.exports = Category;